MYDB=$DB_HOLIDAYS
INPUT_FOLDER=../data/raw_data/school_vacations_and_holidays
OUTPUT_FOLDER=../data/data_csv

##### Read table of holidays and vacations #####
importCSVFile $MYDB $INPUT_FOLDER/joursFeries_and_vacances_scolaires.csv holidays0

##### Add date-specific fields (day/week/month/...) #####
sqlite3 $MYDB "create table holidays as \
    select a.date, a.DeptCode as DEP, \
        yearMonthDay, yearMonth, year, month, weekNum, dayNum, dayOfWeek, \
        cast(a.isVacancesScolaires as int) as isVacancesScolaires, \
        cast(a.isHoliday as int) as isHoliday, \
        case dayOfWeek
            when 0 then 1
            when 6 then 1
            else 0
        end
        as isWeekend
    from holidays0 a, mydays b where a.date = b.date;"
dropTables $MYDB holidays0


##### Make a table of national holidays (only time-varying) #####
sqlite3 $MYDB "create table tmp_day as select distinct dayNum, weekNum, yearMonth, isHoliday as NumHolidays from holidays;"
sqlite3 $MYDB "create table tmp_week as select weekNum, sum(NumHolidays) as NumHolidays from tmp_day group by weekNum;"

sqlite3 $MYDB "create table NationalHolidays_day as   select a.dayNum,    NumHolidays from Scope_day a left join tmp_day     b   on a.dayNum=b.dayNum;"
sqlite3 $MYDB "create table NationalHolidays_week as  select a.weekNum,   NumHolidays from Scope_week a left join tmp_week   b   on a.weekNum=b.weekNum;"

dropTables $MYDB tmp_week tmp_day


##### Do calculations of school days off aggregated over time (at DEP-level) #####
# At day-level
sqlite3 $MYDB "create table tmp_day as \
    select DEP, dayNum, dayOfWeek, weekNum, yearMonth, \
        isHoliday as NumHolidays, isVacancesScolaires as NumVacationsDays, \
        max(isHoliday, isVacancesScolaires, isWeekend) as NumDaysSchoolOff \
    from holidays order by DEP, dayNum;"

# At week-level
sqlite3 $MYDB "create table tmp_week as \
    select DEP, weekNum, sum(NumHolidays) as NumHolidays, sum(NumVacationsDays) as NumVacationsDays, \
        sum(NumDaysSchoolOff) as NumDaysSchoolOff \
    from tmp_day \
    group by DEP, weekNum;"

dropTables $MYDB holidays

##### Merge at all local levels (CodeInsee, CantonId, ARR_ID, DEP) #####
sqlite3 $MYDB "create table SchoolVacations_CantonId_day as \
    select a.CantonId, a.dayNum, NumHolidays, NumVacationsDays, NumDaysSchoolOff \
    from Scope_CantonId_day a left join tmp_day b on a.DEP = b.DEP and a.dayNum = b.dayNum;"

sqlite3 $MYDB "create table SchoolVacations_CantonId_week as \
    select a.CantonId, a.weekNum, NumHolidays, NumVacationsDays, NumDaysSchoolOff \
    from Scope_CantonId_week a left join tmp_week b on a.DEP = b.DEP and a.weekNum = b.weekNum;"

### Clean up
dropTables $MYDB tmp_week tmp_day
for TIME_LEVEL in week day ; do
    dropTables $MYDB Scope_${TIME_LEVEL}
    dropTables $MYDB Scope_CantonId_${TIME_LEVEL}
done
dropTables $MYDB mydays
sqlite3 $MYDB "VACUUM;"
